#!/usr/bin/env python3
"""
直接使用Python创建数据库表
"""

import asyncio
import sys
import os
from pathlib import Path

# 添加项目根目录到Python路径
sys.path.insert(0, str(Path(__file__).parent))

from app.core.database import AsyncSessionLocal
from sqlalchemy import text

async def create_sync_tables():
    """创建同步功能相关表"""
    
    # 读取SQL脚本
    sql_file = Path(__file__).parent.parent / "scripts" / "create_sync_tables.sql"
    
    if not sql_file.exists():
        print(f"❌ SQL文件不存在: {sql_file}")
        return False
    
    with open(sql_file, 'r', encoding='utf-8') as f:
        sql_content = f.read()
    
    # 分割SQL语句
    sql_statements = []
    current_statement = ""
    
    for line in sql_content.split('\n'):
        line = line.strip()
        
        # 跳过注释和空行
        if not line or line.startswith('--') or line.startswith('/*'):
            continue
        
        # 跳过USE语句
        if line.upper().startswith('USE '):
            continue
        
        current_statement += line + " "
        
        # 如果遇到分号，表示一个完整的SQL语句
        if line.endswith(';'):
            sql_statements.append(current_statement.strip())
            current_statement = ""
    
    # 执行SQL语句
    async with AsyncSessionLocal() as db:
        try:
            for i, sql in enumerate(sql_statements):
                if sql:
                    print(f"执行SQL语句 {i+1}/{len(sql_statements)}...")
                    await db.execute(text(sql))
            
            await db.commit()
            print("✅ 数据库表创建成功！")
            
            # 验证表创建
            result = await db.execute(text("""
                SELECT TABLE_NAME 
                FROM information_schema.tables 
                WHERE table_schema = 'data_plexus' AND table_name LIKE 'dp_sync%'
            """))
            
            tables = [row[0] for row in result.fetchall()]
            print(f"✅ 创建了 {len(tables)} 个同步相关表:")
            for table in tables:
                print(f"  - {table}")
            
            return True
            
        except Exception as e:
            print(f"❌ 创建表失败: {e}")
            await db.rollback()
            return False

async def main():
    """主函数"""
    print("开始创建数据同步功能表...")
    success = await create_sync_tables()
    
    if success:
        print("\n🎉 数据库表创建完成！")
    else:
        print("\n❌ 数据库表创建失败！")
        sys.exit(1)

if __name__ == "__main__":
    asyncio.run(main())
